#!/usr/bin/python
# coding=utf-8

from sqlalchemy.types import VARCHAR, DECIMAL
import pandas as pd
import datetime
from common import df_operator as op, db_utils as util

import sys

dtgC45s_Q112 = """
    CREATE TABLE BGTARAS1.PREQ112_DTG_C45S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        HUMI1_DEW_PV DECIMAL(24,6),
        HUMI2_DEW_PV DECIMAL(24,6),
        HUMI2_LIQUID_TEMP DECIMAL(24,6),
        HUMI3_DEW_PV DECIMAL(24,6),
        HUMI3_LIQUID_TEMP DECIMAL(24,6),
        HUMI4_DEW_PV DECIMAL(24,6),
        HUMI4_LIQUID_TEMP DECIMAL(24,6),
        HUMI5_DEW_PV DECIMAL(24,6),
        HUMI5_LIQUID_TEMP DECIMAL(24,6),
        HUMI_FLOW_1_PV DECIMAL(24,6),
        HUMI_FLOW_2_PV DECIMAL(24,6),
        HUMI_FLOW_3_PV DECIMAL(24,6),
        HUMI_FLOW_4_PV DECIMAL(24,6),
        HUMI_FLOW_5_PV DECIMAL(24,6),
        HUMI_FLOW_6_PV DECIMAL(24,6),
        HUMI_FLOW_6_SV DECIMAL(24,6),
        HUMI_FLOW_7_PV DECIMAL(24,6),
        HUMI_FLOW_8_PV DECIMAL(24,6),
        HUMI_H2_1_PV DECIMAL(24,6),
        HUMI_H2_2_PV DECIMAL(24,6),
        HUMI_H2_3_PV DECIMAL(24,6),
        HUMI_H2_4_PV DECIMAL(24,6),
        HUMI_H2_5_PV DECIMAL(24,6),
        HUMI_H2_7_PV DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

dtgC45s_Q212 = """
    CREATE TABLE BGTARAS1.PREQ212_DTG_C45S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PRD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        HUMI_FLOW_2_PV DECIMAL(24,6),
        HUMI_H2_2_PV DECIMAL(24,6),
        HUMI_FLOW_3_PV DECIMAL(24,6),
        HUMI_FLOW_4_PV DECIMAL(24,6),
        HUMI_H2_4_PV DECIMAL(24,6),
        HUMI_FLOW_5_PV DECIMAL(24,6),
        HUMI_H2_5_PV DECIMAL(24,6),
        HUMI_H2_6_PV DECIMAL(24,6),
        HUMI_FLOW_7_PV DECIMAL(24,6),
        HUMI_H2_7_PV DECIMAL(24,6),
        HUMI_FLOW_8_PV DECIMAL(24,6),
        HUMI_H2_8_PV DECIMAL(24,6),
        HUMI_DEW_3_PV DECIMAL(24,6),
        HUMI_DEW_4_PV DECIMAL(24,6),
        HUMI_DEW_5_PV DECIMAL(24,6),
        HUMI_DEW_6_PV DECIMAL(24,6),
        HUMI3_LIQUID_TEMP DECIMAL(24,6),
        HUMI4_LIQUID_TEMP DECIMAL(24,6),
        HUMI5_LIQUID_TEMP DECIMAL(24,6),
        H2_RECYCLE_FLOW DECIMAL(24,6),
        HUMI_H2_3_PV DECIMAL(24,6),
        HUMI_FLOW_6_PV DECIMAL(24,6),
        HUMI_DEW_7_PV DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

dtgC45s_Q312 = """
    CREATE TABLE BGTARAS1.PREQ312_DTG_C45S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PRD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        MIX3_FLOW_PV DECIMAL(24,6),
        MIX3_H2_PV DECIMAL(24,6),
        MIX4_FLOW_PV DECIMAL(24,6),
        MIX5_FLOW_PV DECIMAL(24,6),
        MIX5_H2_PV DECIMAL(24,6),
        MIX6_FLOW_PV DECIMAL(24,6),
        MIX6_H2_PV DECIMAL(24,6),
        ENT_SEAL_N2_FLOW DECIMAL(24,6),
        H2_RECYCLE_PIPE_FLOW DECIMAL(24,6),
        MIX4_H2_PV DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

dtgC45s_Q412 = """
    CREATE TABLE BGTARAS1.PREQ412_DTG_C45S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        HUMI1_DEW_PV DECIMAL(24,6),
        HUMI2_DEW_PV DECIMAL(24,6),
        HUMI3_DEW_PV DECIMAL(24,6),
        HUMI4_DEW_PV DECIMAL(24,6),
        MIX1_H2_PV DECIMAL(24,6),
        MIX2_H2_PV DECIMAL(24,6),
        MIX3_H2_PV DECIMAL(24,6),
        MIX4_H2_PV DECIMAL(24,6),
        MIX5_H2_PV DECIMAL(24,6),
        MIX6_H2_PV DECIMAL(24,6),
        MIX7_H2_PV DECIMAL(24,6),
        ENTRY_N2_FLOW DECIMAL(24,6),
        MIX1_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX2_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX3_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX4_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX5_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX6_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX7_N2_PIPE_FLOW_PV_AVG DECIMAL(24,6),
        MIX1_N2H2_FLOW_PV DECIMAL(24,6),
        MIX2_N2H2_FLOW_PV DECIMAL(24,6),
        MIX3_N2H2_FLOW_PV DECIMAL(24,6),
        MIX4_N2H2_FLOW_PV DECIMAL(24,6),
        MIX5_N2H2_FLOW_PV DECIMAL(24,6),
        MIX6_N2H2_FLOW_PV DECIMAL(24,6),
        MIX7_N2H2_FLOW_PV DECIMAL(24,6),
        H2_RECYC_TFLOW DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

dtgC25s = """
    CREATE TABLE BGTARAS1.PREQ412_DTG_C25S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        RTF1_Z1_FTEMP_PV DECIMAL(24,6),
        RTF1_Z2_FTEMP_PV DECIMAL(24,6),
        RTF1_Z3_FTEMP_PV DECIMAL(24,6),
        RTF2_Z1_FTEMP_PV DECIMAL(24,6),
        RTF2_Z2_FTEMP_PV DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

dtgC55s = """
    CREATE TABLE BGTARAS1.PREQ512_DTG_C55S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        HUMI1_DEW_SV DECIMAL(24,6),
        HUMI2_DEW_SV DECIMAL(24,6),
        HUMI3_DEW_SV DECIMAL(24,6),
        HUMI4_DEW_SV DECIMAL(24,6),
        MIX1_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX2_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX3_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX4_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX5_H2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX6_H2_PIPE_FLOW_PV DECIMAL(24,6),
        HUMI1_STREAM_OPEN_PV DECIMAL(24,6),
        HUMI2_STREAM_OPEN_PV DECIMAL(24,6),
        HUMI3_STREAM_OPEN_PV DECIMAL(24,6),
        HUMI4_STREAM_OPEN_PV DECIMAL(24,6),
        MIX1_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX2_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX3_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX4_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX5_N2_PIPE_FLOW_PV DECIMAL(24,6),
        MIX6_N2_PIPE_FLOW_PV DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

dtgC65s = """
    CREATE TABLE BGTARAS1.PREQ512_DTG_C65S(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        H2_RECYC_FLOW_PV_AVG DECIMAL(24,6),
        ESD1_SEAL_N2_FLOW_AVG DECIMAL(24,6),
        EXT_SEAL_N2_FLOW_AVG DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

dtgRTF = """
    CREATE TABLE BGTARAS1.PREQ512_DTG_RTF(
        UNIT_CODE VARCHAR(4),
        PRODUCE_TIME VARCHAR(20),
        CLASS VARCHAR(5),
        SHIFT VARCHAR(5),
        PROD_COILNO VARCHAR(20),
        ENTRY_COILNO VARCHAR(20),
        RTF_BURNER_STATE_AVG DECIMAL(24,6)
    )NOT LOGGED INITIALLY
    IN "BGTARAS1"
"""

def createQ112_DTG_C45S(start_time, end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ112_DTG_C45S' AND TABSCHEMA='BGTARAS1'"""
    print(sql)
    sql_result = util.query(conn_rds, sql=sql)
    if sql_result.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ112_DTG_C45S")
    else:
        conn_rds.execute(dtgC45s_Q112)

    sql = """WITH
            TMP AS
            (
                SELECT
                    A.UNIT_CODE,
                    A.CLASS,
                    A.SHIFT,
                    A.PRODUCE_TIME,
                    A.PROD_COILNO,
                    A.ENTRY_COILNO,
                    B.START_TIME,
                    B.END_TIME
                FROM
                    BGTARAS1.T_ADS_FACT_SICB_CD0201 a,
                    BGTAROQ112.T_ODS_Q112_BASE B
                WHERE
                    A.UNIT_CODE='Q112'
                AND A.COST_CENTER='MICA'
                AND B.PRD_COILNO = RIGHT(A.PROD_COILNO,9)
                AND a.PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
            )
        SELECT
            A.UNIT_CODE,
            A.CLASS,
            A.SHIFT,
            A.PRODUCE_TIME,
            A.PROD_COILNO,
            A.ENTRY_COILNO,
            AVG(C.HUMI_DEW_1_PV )/10              AS HUMI1_DEW_PV,
            AVG(C.HUMI_DEW_2_PV )/10              AS HUMI2_DEW_PV,
            AVG(C.HUMI2_LIQUID_TEMP )/10          AS HUMI2_LIQUID_TEMP,
            AVG(C.HUMI_DEW_3_PV )/10              AS HUMI3_DEW_PV,
            AVG(C.HUMI3_LIQUID_TEMP )/10          AS HUMI3_LIQUID_TEMP,
            AVG(C.HUMI_DEW_4_PV )/10              AS HUMI4_DEW_PV,
            AVG(C.HUMI4_LIQUID_TEMP )/10          AS HUMI4_LIQUID_TEMP,
            AVG(C.HUMI_DEW_5_PV )/10              AS HUMI5_DEW_PV,
            AVG(C.HUMI5_LIQUID_TEMP )/10          AS HUMI5_LIQUID_TEMP,
            AVG(C.HUMI_FLOW_1_PV )                AS HUMI_FLOW_1_PV,
            AVG(C.HUMI_FLOW_2_PV )                AS HUMI_FLOW_2_PV,
            AVG(C.HUMI_FLOW_3_PV )                AS HUMI_FLOW_3_PV,
            AVG(C.HUMI_FLOW_4_PV )                AS HUMI_FLOW_4_PV,
            AVG(C.HUMI_FLOW_5_PV )                AS HUMI_FLOW_5_PV,
            AVG(C.HUMI_FLOW_6_PV )/10             AS HUMI_FLOW_6_PV,
            AVG(CAST(C.HUMI_FLOW_6_SV AS DOUBLE)) AS HUMI_FLOW_6_SV,
            AVG(C.HUMI_FLOW_7_PV )                AS HUMI_FLOW_7_PV,
            AVG(C.HUMI_FLOW_8_PV )                AS HUMI_FLOW_8_PV,
            AVG(C.HUMI_H2_1_PV )/10               AS HUMI_H2_1_PV,
            AVG(C.HUMI_H2_2_PV )/10               AS HUMI_H2_2_PV,
            AVG(C.HUMI_H2_3_PV )/10               AS HUMI_H2_3_PV,
            AVG(C.HUMI_H2_4_PV )/10               AS HUMI_H2_4_PV,
            AVG(C.HUMI_H2_5_PV )/10               AS HUMI_H2_5_PV,
            AVG(C.HUMI_H2_7_PV )/10               AS HUMI_H2_7_PV
        FROM
            TMP A
        LEFT JOIN
            BGTAROQ112.T_ODS_DTG_C45S C
        ON
            C.UP_DATE BETWEEN A.START_TIME AND A.END_TIME
        GROUP BY
            A.UNIT_CODE,
            A.CLASS,
            A.SHIFT,
            A.PRODUCE_TIME,
            A.PROD_COILNO,
            A.ENTRY_COILNO
        """

    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ112_DTG_C45S", "BGTARAS1")
    print(sql)


def createQ212_DTG_C45S(start_time, end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ212_DTG_C45S' AND TABSCHEMA='BGTARAS1'"""
    print(sql)
    sql_result = util.query(conn_rds, sql=sql)
    if sql_result.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ212_DTG_C45S")
    else:
        conn_rds.execute(dtgC45s_Q212)

    sql = """WITH
                TMP AS
                (
                    SELECT
                        A.UNIT_CODE,
                        A.CLASS,
                        A.SHIFT,
                        A.PRODUCE_TIME,
                        A.PROD_COILNO,
                        A.ENTRY_COILNO,
                        B.START_TIME,
                        B.END_TIME
                    FROM
                        BGTARAS1.T_ADS_FACT_SICB_CD0201 a,
                        BGTAROQ212.T_ODS_Q212_BASE B
                    WHERE
                        A.UNIT_CODE='Q212'
                    AND A.COST_CENTER='MICB'
                    AND B.PRD_COILNO = RIGHT(A.PROD_COILNO,9)
                    AND a.PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
                )
            SELECT
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO AS PRD_COILNO,
                A.ENTRY_COILNO,
                AVG(C.HUMI_FLOW_2_PV )/10 AS HUMI_FLOW_2_PV,
                AVG(C.HUMI_H2_2_PV ) AS HUMI_H2_2_PV,
                AVG(C.HUMI_FLOW_3_PV ) AS HUMI_FLOW_3_PV,
                AVG(C.HUMI_FLOW_4_PV ) AS HUMI_FLOW_4_PV,
                AVG(C.HUMI_H2_4_PV )/10 AS HUMI_H2_4_PV,
                AVG(C.HUMI_FLOW_5_PV ) AS HUMI_FLOW_5_PV,
                AVG(C.HUMI_H2_5_PV )/10 AS HUMI_H2_5_PV,
                AVG(C.HUMI_H2_6_PV )/10 AS HUMI_H2_6_PV,
                AVG(C.HUMI_FLOW_7_PV ) AS HUMI_FLOW_7_PV,
                AVG(C.HUMI_H2_7_PV )/10 AS HUMI_H2_7_PV,
                AVG(C.HUMI_FLOW_8_PV ) AS HUMI_FLOW_8_PV,
                AVG(C.HUMI_H2_8_PV )/10 AS HUMI_H2_8_PV,
                AVG(C.HUMI_DEW_3_PV )/10 AS HUMI_DEW_3_PV,
                AVG(C.HUMI_DEW_4_PV )/10 AS HUMI_DEW_4_PV,
                AVG(C.HUMI_DEW_5_PV )/10 AS HUMI_DEW_5_PV,
                AVG(C.HUMI_DEW_6_PV )/10 AS HUMI_DEW_6_PV,
                AVG(C.HUMI3_LIQUID_TEMP )/10 AS HUMI3_LIQUID_TEMP,
                AVG(C.HUMI4_LIQUID_TEMP )/10 AS HUMI4_LIQUID_TEMP,
                AVG(C.HUMI5_LIQUID_TEMP )/10 AS HUMI5_LIQUID_TEMP,
                AVG(CAST(C.H2_RECYCLE_FLOW AS DOUBLE)) AS H2_RECYCLE_FLOW,
                AVG(C.HUMI_H2_3_PV )/10 AS HUMI_H2_3_PV,
                AVG(C.HUMI_FLOW_6_PV ) AS HUMI_FLOW_6_PV,
                AVG(C.HUMI_DEW_7_PV )/10 AS HUMI_DEW_7_PV
            FROM
                TMP A
            LEFT JOIN
                BGTAROQ212.T_ODS_DTG_C45S C
            ON
                C.UP_DATE BETWEEN A.START_TIME AND A.END_TIME
            GROUP BY
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO,
                A.ENTRY_COILNO
        """

    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ212_DTG_C45S", "BGTARAS1")
    print(sql)


def createQ312_DTG_C45S(start_time, end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ312_DTG_C45S' AND TABSCHEMA='BGTARAS1'"""
    print(sql)
    sql_result = util.query(conn_rds, sql=sql)
    if sql_result.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ312_DTG_C45S")
    else:
        conn_rds.execute(dtgC45s_Q312)

    sql = """WITH
            TMP AS
            (
                SELECT
                    A.UNIT_CODE,
                    A.CLASS,
                    A.SHIFT,
                    A.PRODUCE_TIME,
                    A.PROD_COILNO,
                    A.ENTRY_COILNO,
                    B.START_TIME,
                    B.END_TIME
                FROM
                    BGTARAS1.T_ADS_FACT_SICB_CD0201 a,
                    BGTAROQ312.T_ODS_Q312_BASE B
                WHERE
                    A.UNIT_CODE='Q312'
                AND A.COST_CENTER='MIHA'
                AND B.PRD_COILNO = RIGHT(A.PROD_COILNO,9)
                AND a.PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
            )
            SELECT
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO AS PRD_COILNO,
                A.ENTRY_COILNO,
                AVG(C.MIX3_FLOW_PV )               AS MIX3_FLOW_PV,
                AVG(C.MIX3_H2_PV )/10               AS MIX3_H2_PV,
                AVG(C.MIX4_FLOW_PV )               AS MIX4_FLOW_PV,
                AVG(C.MIX5_FLOW_PV )               AS MIX5_FLOW_PV,
                AVG(C.MIX5_H2_PV )/10               AS MIX5_H2_PV,
                AVG(C.MIX6_FLOW_PV )               AS MIX6_FLOW_PV,
                AVG(C.MIX6_H2_PV )/10               AS MIX6_H2_PV,
                AVG(CAST(C.ENT_SEAL_N2_FLOW AS DOUBLE))               AS ENT_SEAL_N2_FLOW,
                AVG(CAST(C.H2_RECYCLE_PIPE_FLOW AS DOUBLE))               AS H2_RECYCLE_PIPE_FLOW,
                AVG(C.MIX4_H2_PV )/10               AS MIX4_H2_PV
            FROM
                TMP A
            LEFT JOIN
                BGTAROQ312.T_ODS_DTG_C45S C
            ON
                C.UP_DATE BETWEEN A.START_TIME AND A.END_TIME
            GROUP BY
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO,
                A.ENTRY_COILNO
            """

    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ312_DTG_C45S", "BGTARAS1")
    print(sql)


def createQ412_DTG_C45S(start_time, end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ412_DTG_C45S' AND TABSCHEMA='BGTARAS1'"""
    sql2 = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ412_DTG_C25S' AND TABSCHEMA='BGTARAS1'"""
    sql_result = util.query(conn_rds, sql=sql)
    sql_result2 = util.query(conn_rds, sql=sql2)
    if sql_result.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ412_DTG_C45S")
    else:
        conn_rds.execute(dtgC45s_Q412)
    if sql_result2.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ412_DTG_C25S")
    else:
        conn_rds.execute(dtgC25s)

    sql_1 = """WITH
            TMP AS
            (
                SELECT
                    A.UNIT_CODE,
                    A.CLASS,
                    A.SHIFT,
                    A.PRODUCE_TIME,
                    A.PROD_COILNO,
                    A.ENTRY_COILNO,
                    CONCAT(B.START_TIME_DATE, B.START_TIME_TIME) AS START_TIME,
                    CONCAT(B.END_TIME_DATE, B.END_TIME_TIME)     AS END_TIME
                FROM
                    BGTARAS1.T_ADS_FACT_SICB_CD0201 a,
                    BGTAROQ412.DTG_DCDT B
                WHERE
                    A.UNIT_CODE='Q412'
                AND A.COST_CENTER='MIHB'
                AND B.PRD_COILNO=A.PROD_COILNO
                AND a.PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
            )
            SELECT
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO,
                A.ENTRY_COILNO,
                AVG(CAST(C.HUMI1_DEW_SV AS DOUBLE)/10)      AS HUMI1_DEW_PV,--加湿器露点设定
                AVG(CAST(C.HUMI2_DEW_SV AS DOUBLE)/10)      AS HUMI2_DEW_PV,
                AVG(CAST(C.HUMI3_DEW_SV AS DOUBLE)/10)      AS HUMI3_DEW_PV,
                AVG(CAST(C.HUMI4_DEW_SV AS DOUBLE)/10)      AS HUMI4_DEW_PV,
                AVG(CAST(C.MIX1_H2_SV AS DOUBLE)/10)        AS MIX1_H2_PV,--混气站氢气浓度
                AVG(CAST(C.MIX2_H2_SV AS DOUBLE)/10)        AS MIX2_H2_PV,
                AVG(CAST(C.MIX3_H2_SV AS DOUBLE)/10)        AS MIX3_H2_PV,
                AVG(CAST(C.MIX4_H2_SV AS DOUBLE)/10)        AS MIX4_H2_PV,
                AVG(CAST(C.MIX5_H2_SV AS DOUBLE)/10)        AS MIX5_H2_PV,
                AVG(CAST(C.MIX6_H2_SV AS DOUBLE)/10)        AS MIX6_H2_PV,
                AVG(CAST(C.MIX7_H2_SV AS DOUBLE)/10)        AS MIX7_H2_PV, --入口密封氮气流量
                AVG(CAST(C.ENTRY_N2_FLOW AS DOUBLE)/10)     AS ENTRY_N2_FLOW,
                AVG(C.MIX1_N2H2_FLOW_PV*10*(1-CAST(C.MIX1_H2_PV AS DOUBLE)/100)/10) AS MIX1_N2_PIPE_FLOW_PV_AVG,-- 混气站氮气流量
                AVG(C.MIX2_N2H2_FLOW_PV*10*(1-CAST(C.MIX2_H2_PV AS DOUBLE)/100)/10) AS MIX2_N2_PIPE_FLOW_PV_AVG,
                AVG(C.MIX3_N2H2_FLOW_PV*10*(1-CAST(C.MIX3_H2_PV AS DOUBLE)/100)/10) AS MIX3_N2_PIPE_FLOW_PV_AVG,
                AVG(C.MIX4_N2H2_FLOW_PV*10*(1-CAST(C.MIX4_H2_PV AS DOUBLE)/100)/10) AS MIX4_N2_PIPE_FLOW_PV_AVG,
                AVG(C.MIX5_N2H2_FLOW_PV*10*(1-CAST(C.MIX5_H2_PV AS DOUBLE)/100)/10) AS MIX5_N2_PIPE_FLOW_PV_AVG,
                AVG(C.MIX6_N2H2_FLOW_PV*10*(1-CAST(C.MIX6_H2_PV AS DOUBLE)/100)/10) AS MIX6_N2_PIPE_FLOW_PV_AVG,
                AVG(C.MIX7_N2H2_FLOW_PV*10*(1-CAST(C.MIX7_H2_PV AS DOUBLE)/100)/10) AS MIX7_N2_PIPE_FLOW_PV_AVG,
                AVG(CAST(C.MIX1_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX1_N2H2_FLOW_PV,--混气N2H2总流量
                AVG(CAST(C.MIX2_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX2_N2H2_FLOW_PV,
                AVG(CAST(C.MIX3_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX3_N2H2_FLOW_PV,
                AVG(CAST(C.MIX4_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX4_N2H2_FLOW_PV,
                AVG(CAST(C.MIX5_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX5_N2H2_FLOW_PV,
                AVG(CAST(C.MIX6_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX6_N2H2_FLOW_PV,
                AVG(CAST(C.MIX7_N2H2_FLOW_PV AS DOUBLE)/10) AS MIX7_N2H2_FLOW_PV,--氢气回收管道流量
                AVG(CAST(C.H2_RECYC_TFLOW AS DOUBLE)/10)    AS H2_RECYC_TFLOW
            FROM
                TMP A
            LEFT JOIN
                BGTAROQ412.DTG_C45S c
            ON
                C.UP_DATE BETWEEN A.START_TIME AND A.END_TIME
            GROUP BY
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO,
                A.ENTRY_COILNO
            """

    sql_2 = """WITH
                TMP AS
                (
                    SELECT
                        A.UNIT_CODE,
                        A.CLASS,
                        A.SHIFT,
                        A.PRODUCE_TIME,
                        A.PROD_COILNO,
                        A.ENTRY_COILNO,
                        CONCAT(B.START_TIME_DATE, B.START_TIME_TIME) AS START_TIME,
                        CONCAT(B.END_TIME_DATE, B.END_TIME_TIME)     AS END_TIME
                    FROM
                        BGTARAS1.T_ADS_FACT_SICB_CD0201 a,
                        BGTAROQ412.DTG_DCDT B
                    WHERE
                        A.UNIT_CODE='Q412'
                    AND A.COST_CENTER='MIHB'
                    AND B.PRD_COILNO=A.PROD_COILNO
                    AND a.PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
                )
                SELECT
                    A.UNIT_CODE,
                    A.CLASS,
                    A.SHIFT,
                    A.PRODUCE_TIME,
                    A.PROD_COILNO,
                    A.ENTRY_COILNO,
                    AVG(CAST(D.RTF1_Z1_FTEMP_PV AS DOUBLE)/10) AS RTF1_Z1_FTEMP_PV,
                    AVG(CAST(D.RTF1_Z2_FTEMP_PV AS DOUBLE)/10) AS RTF1_Z2_FTEMP_PV,
                    AVG(CAST(D.RTF1_Z3_FTEMP_PV AS DOUBLE)/10) AS RTF1_Z3_FTEMP_PV,
                    AVG(CAST(D.RTF2_Z1_FTEMP_PV AS DOUBLE)/10) AS RTF2_Z1_FTEMP_PV,
                    AVG(CAST(D.RTF2_Z2_FTEMP_PV AS DOUBLE)/10) AS RTF2_Z2_FTEMP_PV
                FROM
                    TMP A
                LEFT JOIN
                    BGTAROQ412.DTG_C25S D
                ON
                    D.UP_DATE BETWEEN A.START_TIME AND A.END_TIME
                GROUP BY
                    A.UNIT_CODE,
                    A.CLASS,
                    A.SHIFT,
                    A.PRODUCE_TIME,
                    A.PROD_COILNO,
                    A.ENTRY_COILNO
                """

    df_result = util.query(conn_rds, sql=sql_1)
    util.insert(df_result, conn_rds, "PREQ412_DTG_C45S", "BGTARAS1")
    df_result2 = util.query(conn_rds, sql=sql_2)
    util.insert(df_result2, conn_rds, "PREQ412_DTG_C25S", "BGTARAS1")


def createQ512_DTG_C55S(start_time, end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ512_DTG_C55S' AND TABSCHEMA='BGTARAS1'"""
    sql2 = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ512_DTG_C65S' AND TABSCHEMA='BGTARAS1'"""
    print(sql)
    sql_result = util.query(conn_rds, sql=sql)
    sql_result2 = util.query(conn_rds, sql=sql2)
    if sql_result.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ512_DTG_C55S")
    else:
        conn_rds.execute(dtgC55s)
    if sql_result2.__len__() > 0:
        conn_rds.execute("drop table BGTARAS1.PREQ512_DTG_C65S")
    else:
        conn_rds.execute(dtgC65s)

    sql_1 = """
            WITH
                TMP AS
                (
                    SELECT
                        A.UNIT_CODE,
                        A.CLASS,
                        A.SHIFT,
                        A.PRODUCE_TIME,
                        A.PROD_COILNO,
                        A.ENTRY_COILNO,
                        CONCAT(B.START_TIME_DATE, B.START_TIME_TIME) AS START_TIME,
                        CONCAT(B.END_TIME_DATE, B.END_TIME_TIME)     AS END_TIME
                    FROM
                        BGTARAS1.T_ADS_FACT_SICB_CD0201 A,
                        BGTAROQ512.T_ODS_DTG_DCDT B
                    WHERE
                        a.UNIT_CODE='Q512'
                    AND A.COST_CENTER='MIJA'
                    AND b.PRD_COILNO=a.PROD_COILNO
                    AND a.PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
                )
            SELECT
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO,
                A.ENTRY_COILNO,
                AVG(CAST(C.HUMI1_DEW_SV AS DOUBLE)/10) AS HUMI1_DEW_SV,--加湿器露点设定
                AVG(CAST(C.HUMI2_DEW_SV AS DOUBLE)/10) AS HUMI2_DEW_SV,
                AVG(CAST(C.HUMI3_DEW_SV AS DOUBLE)/10) AS HUMI3_DEW_SV,
                AVG(CAST(C.HUMI4_DEW_SV AS DOUBLE)/10) AS HUMI4_DEW_SV,
                AVG(CAST(C.mix1_h2_pipe_flow_pv AS DOUBLE)/(C.mix1_n2h2_flow_rtf1_pv+C.mix1_n2h2_flow_sf1_pv))*
                10                                                             AS MIX1_H2_PIPE_FLOW_PV,--混气站氢气浓度
                AVG(CAST(C.mix2_h2_pipe_flow_pv AS DOUBLE)/C.mix2_n2h2_flow_pv)*100 AS MIX2_H2_PIPE_FLOW_PV,
                AVG(CAST(C.mix3_h2_pipe_flow_pv AS DOUBLE)/C.mix3_n2h2_flow_pv)*100 AS MIX3_H2_PIPE_FLOW_PV,
                AVG(CAST(C.mix4_h2_pipe_flow_pv AS DOUBLE)/C.mix4_n2h2_flow_pv)*100 AS MIX4_H2_PIPE_FLOW_PV,
                AVG(CAST(C.mix5_h2_pipe_flow_pv AS DOUBLE)/C.mix5_n2h2_flow_pv)*100 AS MIX5_H2_PIPE_FLOW_PV,
                AVG(CAST(C.mix6_h2_pipe_flow_pv AS DOUBLE)/C.mix6_n2h2_flow_pv)*100 AS MIX6_H2_PIPE_FLOW_PV,
                AVG(CAST(C.HUMI1_STREAM_OPEN_PV AS DOUBLE)/10)                      AS HUMI1_STREAM_OPEN_PV,--
                -- 加湿器露点蒸汽阀门开度
                AVG(CAST(C.HUMI2_STREAM_OPEN_PV AS DOUBLE)/10) AS HUMI2_STREAM_OPEN_PV,
                AVG(CAST(C.HUMI3_STREAM_OPEN_PV AS DOUBLE)/10) AS HUMI3_STREAM_OPEN_PV,
                AVG(CAST(C.HUMI4_STREAM_OPEN_PV AS DOUBLE)/10) AS HUMI4_STREAM_OPEN_PV,
                AVG(CAST(C.MIX1_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX1_N2_PIPE_FLOW_PV,--混气站氮气流量
                AVG(CAST(C.MIX2_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX2_N2_PIPE_FLOW_PV,
                AVG(CAST(C.MIX3_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX3_N2_PIPE_FLOW_PV,
                AVG(CAST(C.MIX4_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX4_N2_PIPE_FLOW_PV,
                AVG(CAST(C.MIX5_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX5_N2_PIPE_FLOW_PV,
                AVG(CAST(C.MIX6_N2_PIPE_FLOW_PV AS DOUBLE)/10) AS MIX6_N2_PIPE_FLOW_PV
            FROM
                TMP a
            LEFT JOIN
                BGTAROQ512.T_ODS_DTG_C55S C
            ON
                c.UP_DATE BETWEEN a.START_TIME AND a.END_TIME
            GROUP BY
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO,
                A.ENTRY_COILNO
            """

    sql_2 = """WITH
                TMP AS
                (
                    SELECT
                        A.UNIT_CODE,
                        A.CLASS,
                        A.SHIFT,
                        A.PRODUCE_TIME,
                        A.PROD_COILNO,
                        A.ENTRY_COILNO,
                        CONCAT(B.START_TIME_DATE, B.START_TIME_TIME) AS START_TIME,
                        CONCAT(B.END_TIME_DATE, B.END_TIME_TIME)     AS END_TIME
                    FROM
                        BGTARAS1.T_ADS_FACT_SICB_CD0201 A,
                        BGTAROQ512.T_ODS_DTG_DCDT B
                    WHERE
                        a.UNIT_CODE='Q512'
                    AND A.COST_CENTER='MIJA'
                    AND b.PRD_COILNO=a.PROD_COILNO
                    AND a.PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
                )
            SELECT
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO,
                A.ENTRY_COILNO,
                AVG(CAST(C.H2_RECYC_FLOW_PV AS DOUBLE))    AS H2_RECYC_FLOW_PV_AVG,
                AVG(CAST(C.ESD1_SEAL_N2_FLOW AS DOUBLE))   AS ESD1_SEAL_N2_FLOW_AVG,
                AVG(CAST(C.EXT_SEAL_N2_FLOW AS DOUBLE)/10) AS EXT_SEAL_N2_FLOW_AVG
            FROM
                TMP a
            LEFT JOIN
                BGTAROQ512.T_ODS_DTG_C65S C
            ON
                c.UP_DATE BETWEEN a.START_TIME AND a.END_TIME
            GROUP BY
                A.UNIT_CODE,
                A.CLASS,
                A.SHIFT,
                A.PRODUCE_TIME,
                A.PROD_COILNO,
                A.ENTRY_COILNO
            """
    df_result = util.query(conn_rds, sql=sql_1)
    util.insert(df_result, conn_rds, "PREQ512_DTG_C55S", "BGTARAS1")
    df_result2 = util.query(conn_rds, sql=sql_2)
    util.insert(df_result2, conn_rds, "PREQ512_DTG_C65S", "BGTARAS1")

def createQ512_DTG_C45S(start_time,end_time):
    sql = """SELECT * FROM SYSCAT.TABLES WHERE TABNAME='PREQ512_DTG_RTF' AND TABSCHEMA='BGTARAS1'"""
    sql_result = util.query(conn_rds, sql=sql)
    if sql_result.__len__() > 0 :
        conn_rds.execute("drop table BGTARAS1.PREQ512_DTG_RTF")


    sql = """
    WITH
    tmp1 AS
    (
        SELECT
            UNIT_CODE,
            PRODUCE_TIME,
            CLASS,
            SHIFT,
            PROD_COILNO,
            ENTRY_COILNO
        FROM
            BGTARAS1.T_ADS_FACT_SICB_CD0201
        WHERE
            UNIT_CODE='Q512'
        AND PRODUCE_TIME BETWEEN """ + start_time + """ AND """ + end_time + """
    )
    ,
    tmp2 AS
    (
        SELECT
            *
        FROM
            (
                SELECT
                    b.PRODUCE_TIME,
                    b.CLASS,
                    b.unit_code,
                    b.shift,
                    a.PRD_COILNO,
                    a.ENT_COILNO,
                    a.DMY_USE_CNT,
                    a.RTF1_Z1_BURNER_STATE_1,
                    a.RTF1_Z1_BURNER_STATE_2,
                    a.RTF1_Z2_BURNER_STATE_1,
                    a.RTF1_Z2_BURNER_STATE_2,
                    row_number() over(partition BY a.ENT_COILNO,a.PRD_COILNO ORDER BY ABS(to_date
                    (a.UP_DATE, 'YYYYMMDDHH24MISS')-to_date(base.END_TIME,'YYYYMMDDHH24MISS')) DESC
                    ) AS num,
                    (BGTARAS1.F_SUM_NUMBER(BGTARAS1.NUMBER_TO_BIT(a.RTF1_Z1_BURNER_STATE_1))+
                    BGTARAS1.F_SUM_NUMBER(BGTARAS1.NUMBER_TO_BIT(a.RTF1_Z1_BURNER_STATE_2))+
                    BGTARAS1.F_SUM_NUMBER(BGTARAS1.NUMBER_TO_BIT(a.RTF1_Z2_BURNER_STATE_1))+
                    BGTARAS1.F_SUM_NUMBER(BGTARAS1.NUMBER_TO_BIT(a.RTF1_Z2_BURNER_STATE_2))) AS
                    val1
                FROM
                    BGTAROQ512.T_ODS_DTG_CRT1 a
                INNER JOIN
                    BGTAROQ512.T_ODS_Q512_BASE base
                ON
                    a.ENT_COILNO = base.ENT_COILNO
                AND a.PRD_COILNO = base.PRD_COILNO
                RIGHT JOIN
                    tmp1 b
                ON
                    a.PRD_COILNO = b.PROD_COILNO
                AND a.ENT_COILNO = b.ENTRY_COILNO )
        WHERE
            num = 1
    )
    ,
    tmp3 AS
    (
        SELECT
            *
        FROM
            (
                SELECT
                    b.PRODUCE_TIME,
                    b.CLASS,
                    b.unit_code,
                    b.shift,
                    a.PRD_COILNO,
                    a.ENT_COILNO,
                    a.DMY_USE_CNT,
                    row_number() over(partition BY a.ENT_COILNO,a.PRD_COILNO ORDER BY ABS(to_date
                    (a.UP_DATE, 'YYYYMMDDHH24MISS')-to_date(base.END_TIME,'YYYYMMDDHH24MISS')) DESC
                    ) AS num,
                    (BGTARAS1.F_SUM_NUMBER(BGTARAS1.NUMBER_TO_BIT(a.RTF2_Z1_BURNER_STATE))+
                    BGTARAS1.F_SUM_NUMBER(BGTARAS1.NUMBER_TO_BIT(a.RTF2_Z2_BURNER_STATE))) AS val2
                FROM
                    BGTAROQ512.T_ODS_DTG_CRT2 a
                INNER JOIN
                    BGTAROQ512.T_ODS_Q512_BASE base
                ON
                    a.ENT_COILNO = base.ENT_COILNO
                AND a.PRD_COILNO = base.PRD_COILNO
                RIGHT JOIN
                    tmp1 b
                ON
                    a.PRD_COILNO = b.PROD_COILNO
                AND a.ENT_COILNO = b.ENTRY_COILNO )
        WHERE
            num = 1
    )
    SELECT
        a.unit_code,
        A.PRODUCE_TIME,
        A.CLASS,
        A.SHIFT,
        A.PRD_COILNO,
        A.ENT_COILNO,
        ROUND(CAST(a.val1+b.val2 AS DOUBLE),0) AS RTF_BURNER_STATE_AVG 
    FROM
        tmp2 a
    INNER JOIN
        tmp3 b
    ON
        a.PRD_COILNO=b.PRD_COILNO
    AND a.ENT_COILNO=b.ENT_COILNO
    AND a.DMY_USE_CNT=b.DMY_USE_CNT
    """
    df_result = util.query(conn_rds, sql=sql)
    util.insert(df_result, conn_rds, "PREQ512_DTG_RTF", "BGTARAS1")


def preQ12(start_time, end_time,unit):
    global conn_rds
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    print(datetime.datetime.now())
    try:
        if unit == 'Q112':
            createQ112_DTG_C45S(start_time, end_time)
        elif unit == 'Q212':
            createQ212_DTG_C45S(start_time, end_time)
        elif unit == 'Q312':
            createQ312_DTG_C45S(start_time, end_time)
        elif unit == 'Q412':
            createQ412_DTG_C45S(start_time, end_time)
        elif unit == 'Q512':
            createQ512_DTG_C55S(start_time, end_time)
            createQ512_DTG_C45S(start_time, end_time)
        print(datetime.datetime.now())
        conn_rds.close()
    except Exception as e:
        print("PreQ_12 error:"+ e)


def main():
    # PreQ_12('20211014200514', '20211015080514', 'Q112')
    # PreQ_12('20211014200514', '20211015080514', 'Q212')
    # PreQ_12('20211014200514', '20211015080514', 'Q312')
    # PreQ_12('20211014200514', '20211014200514', 'Q412')
    preQ12('20211027191640', '20211027191640', 'Q512')


if __name__ == '__main__':
    main()
